package com.wifi.books.dao;

import com.wifi.books.entity.BookEntity;
import com.wifi.books.entity.SecondTypeEntity;
import com.wifi.books.entity.TypeEntity;
import com.wifi.unti.DBHelper;
import com.wifi.unti.StringUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class BookDAO {
    private BookEntity bookEntity =new BookEntity();
    private static BookDAO bookDAO;
    private BookDAO(){}
    public static BookDAO getBookDAO(){
        if (bookDAO==null){
            return  bookDAO=new BookDAO();
        }else {
            return bookDAO;
        }
    }

    /**
     *
     * @param start 分页开始
     * @param pageSize 每页几条
     * @return 热门小说的list集合，使用时需要强转
     * @throws SQLException
     */
    public List<Object> queryBookList(Integer start,Integer pageSize) throws SQLException {
        String sql=" SELECT b.* , s.parent_id, s.second_type_name,f.type_name FROM tb_books b, tb_book_stype s , tb_book_ftype f WHERE b.stype_id = s.type_id AND b.ftype_id = f.type_id ORDER BY bid limit ? , ?";
        List<Object> list = DBHelper.selectAll(sql, bookEntity, start, pageSize);
        if (list!=null){
            return list;
        }
        return null;
    }
    public List<Object> queryBookListByBookType(Integer ftypeId,Integer start,Integer pageSize) throws SQLException {
        String sql=" SELECT b.* , s.parent_id, s.second_type_name,f.type_name FROM tb_books b, tb_book_stype s , tb_book_ftype f WHERE b.stype_id = s.type_id AND b.ftype_id = f.type_id AND b.ftype_id =  ? limit ? , ?";
        List<Object> list = DBHelper.selectAll(sql, bookEntity,ftypeId, start, pageSize);
        if (list!=null){
            return list;
        }
        return null;
    }
    public List<Object> queryBookListByBookType(Integer ftypeId, Integer stypeId, Integer start, Integer pageSize) throws SQLException {
        String sql=" SELECT b.* , s.parent_id, s.second_type_name,f.type_name FROM tb_books b, tb_book_stype s , tb_book_ftype f WHERE b.stype_id = s.type_id AND b.ftype_id = f.type_id AND b.ftype_id =  ? AND b.stype_id = ? limit ? , ?";
        List<Object> list = DBHelper.selectAll(sql, bookEntity,ftypeId,stypeId, start, pageSize);
        if (list!=null){
            return list;
        }
        return null;
    }

    /**
     *
     * @param bookId 需要修改的bookID
     * @return 受影响的行数
     */
    public Integer updateBook(Integer bookId,BookEntity book) throws SQLException {
        String sql ="UPDATE `shenzhou`.`tb_books` SET book_name = ? ,author_name = ? , ftype_id = ? ," +
                " stype_id = ? , status = ? , is_vip = ? , description = ? , cover_url = ? ," +
                " word_count = ? ," +
                "  status_up = ?, last_update_time= now() WHERE `book_id` = ? ";

        int update = DBHelper.update(sql,  book.getBookName(), book.getAuthorName(), book.getFtypeId(), book.getStypeId(), book.getStatus(),
                book.getIsVip(), book.getDescription()
                , book.getCoverUrl(), book.getWordCount(), book.getStatusUp(),bookId);
        return update;
    }
    public Long addBook (BookEntity bookEntity) throws SQLException {
        String sql ="INSERT INTO `shenzhou`.`tb_books`" +
                "(`book_id`, `book_name`, `author_name`, `ftype_id`, `stype_id`, `status`, `is_vip`, `description`," +
                " `cover_url`,  `word_count`, " +
                "  `read_count` , `status_up`, `last_update_time`, `creat_time`) " +
                "VALUES (? , ? , ?, ?, ?, ?, ?, ?," +
                " ?,  ?," +
                "  0,? , now() ,now())";
        Long aLong = DBHelper.insert(sql, bookEntity.getBookId(), bookEntity.getBookName(), bookEntity.getAuthorName(), bookEntity.getFtypeId(), bookEntity.getStypeId()
                , bookEntity.getStatus(), bookEntity.getIsVip(), bookEntity.getDescription(), bookEntity.getCoverUrl(), bookEntity.getWordCount()
                , bookEntity.getStatusUp());
        return aLong;
    }
    public Integer deleteBookById(Integer bookId) throws SQLException {
        String sql ="UPDATE `shenzhou`.`tb_books` SET `status_up` = 0 WHERE `bid` = ?";
        int update = DBHelper.update(sql, bookId);
        return update;
    }


    public List<Object> queryBookByInfo(String bookInfo) throws SQLException {
//        StringBuffer stringBuffer = new StringBuffer("%");
//        stringBuffer.append(bookInfo).append("%");
//        System.out.println(stringBuffer.toString());
//        String sql = " SELECT b.* , s.parent_id, s.second_type_name,f.type_name FROM tb_books b, tb_book_stype s , tb_book_ftype f WHERE b.stype_id = s.type_id AND b.ftype_id = f.type_id AND book_name LIKE ?  ";
        String sql = "SELECT b.* , s.parent_id, s.second_type_name,f.type_name FROM tb_books b, tb_book_stype s , tb_book_ftype f WHERE b.stype_id = s.type_id AND b.ftype_id = f.type_id AND";
        sql= (sql+ bookInfo);
        List<Object> list = DBHelper.selectAll(sql, new BookEntity());
        return list;
    }

    public Integer booksCount() throws SQLException {
        String sql ="select count(*) from tb_books";
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDataSource());
        Long aLong = queryRunner.query(sql, new ScalarHandler<Long>());
        return aLong.intValue();
    }
    public Integer bookChapterCount(Integer bookId) throws SQLException {
        String sql ="select count(*) from tb_book_chapter where book_id =?";
        QueryRunner queryRunner =new QueryRunner(DBHelper.getDataSource());
        Long aLong = queryRunner.query(sql, new ScalarHandler<Long>(), bookId);
        return aLong.intValue();
    }

    public List<Object> bookTypeList() throws SQLException {
        String sql=" SELECT *  FROM tb_book_ftype ";
        List<Object> list = DBHelper.selectAll(sql,new TypeEntity());
        if (list!=null){
            return list;
        }
        return null;
    }
    public List<Object> bookSecondTypeList(Integer ftypeId) throws SQLException {
        String sql=" SELECT * FROM tb_book_stype where parent_id = ?";
        List<Object> list = DBHelper.selectAll(sql,new SecondTypeEntity(),ftypeId);
        if (list!=null){
            return list;
        }
        return null;
    }
    public BookEntity queryBookById(Integer bookId) throws SQLException {
        String sql ="select * from tb_books where book_id = ?";
        Object select = DBHelper.select(sql, new BookEntity(), bookId);
        return (BookEntity)select;
    }
    public List<Object> recommendBookList(Integer fType,Integer isRecommend) throws SQLException {
        String sql="SELECT * FROM tb_books where ftype_id = ? AND is_recommend = ? limit 1 , 10";
        List<Object> list = DBHelper.selectAll(sql, bookEntity, fType, isRecommend);
        if (list!=null){
            return list;
        }
        return null;
    }

}
